package net.suncaper.mvc.mapper.impl;

import net.suncaper.mvc.mapper.MessageMapper;
import net.suncaper.mvc.model.domain.Message;
import net.suncaper.mvc.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Message表数据库访问接口实现类
 */
public class MessageMapperImpl implements MessageMapper {

    private static final QueryRunner RUNNER = new QueryRunner();

    /**
     * 添加消息
     * @param message 消息信息
     * @return 添加结果
     */
    @Override
    public boolean insertMessage(Message message) {
        String sql = "INSERT INTO message(user_id, title, content, type, related_id, is_read, create_time) VALUES (?, ?, ?, ?, ?, ?, ?)";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            // 确保is_read值是0或1
            int isRead = message.getIsRead() != null && message.getIsRead() ? 1 : 0;
            
            rows = RUNNER.update(
                    connection,
                    sql,
                    message.getUserId(), 
                    message.getTitle(),
                    message.getContent(),
                    message.getType(),
                    message.getRelatedId(),
                    isRead,
                    message.getCreateTime()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 根据消息ID获取消息
     * @param id 消息ID
     * @return 消息信息
     */
    @Override
    public Message selectMessageById(Long id) {
        String sql = "SELECT * FROM message WHERE id = ?";
        Connection connection = JDBCUtils.getConnection();
        Message message = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setLong(1, id);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                message = mapResultSetToMessage(rs);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return message;
    }

    /**
     * 根据用户ID获取消息列表
     * @param userId 用户ID
     * @return 消息列表
     */
    @Override
    public List<Message> selectMessagesByUserId(String userId) {
        String sql = "SELECT * FROM message WHERE user_id = ? ORDER BY create_time DESC";
        Connection connection = JDBCUtils.getConnection();
        List<Message> messages = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                messages.add(mapResultSetToMessage(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return messages;
    }

    /**
     * 根据用户ID和消息类型获取消息列表
     * @param userId 用户ID
     * @param type 消息类型
     * @return 消息列表
     */
    @Override
    public List<Message> selectMessagesByUserIdAndType(String userId, String type) {
        String sql = "SELECT * FROM message WHERE user_id = ? AND type = ? ORDER BY create_time DESC";
        Connection connection = JDBCUtils.getConnection();
        List<Message> messages = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, userId);
            pstmt.setString(2, type);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                messages.add(mapResultSetToMessage(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return messages;
    }

    /**
     * 根据用户ID获取未读消息列表
     * @param userId 用户ID
     * @return 未读消息列表
     */
    @Override
    public List<Message> selectUnreadMessagesByUserId(String userId) {
        String sql = "SELECT * FROM message WHERE user_id = ? AND is_read = 0 ORDER BY create_time DESC";
        Connection connection = JDBCUtils.getConnection();
        List<Message> messages = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                messages.add(mapResultSetToMessage(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return messages;
    }

    /**
     * 获取用户未读消息数量
     * @param userId 用户ID
     * @return 未读消息数量
     */
    @Override
    public int countUnreadMessagesByUserId(String userId) {
        String sql = "SELECT COUNT(*) FROM message WHERE user_id = ? AND is_read = 0";
        Connection connection = JDBCUtils.getConnection();
        int count = 0;
        
        try {
            Long result = RUNNER.query(
                connection,
                sql,
                new ScalarHandler<Long>(),
                userId
            );
            if (result != null) {
                count = result.intValue();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return count;
    }

    /**
     * 标记消息为已读
     * @param id 消息ID
     * @return 更新结果
     */
    @Override
    public boolean markMessageAsRead(Long id) {
        String sql = "UPDATE message SET is_read = 1, update_time = ? WHERE id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        
        try {
            rows = RUNNER.update(
                connection,
                sql,
                new Timestamp(System.currentTimeMillis()),
                id
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 标记用户的所有消息为已读
     * @param userId 用户ID
     * @return 更新结果
     */
    @Override
    public boolean markAllMessagesAsRead(String userId) {
        String sql = "UPDATE message SET is_read = 1, update_time = ? WHERE user_id = ? AND is_read = 0";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        
        try {
            rows = RUNNER.update(
                connection,
                sql,
                new Timestamp(System.currentTimeMillis()),
                userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 删除消息
     * @param id 消息ID
     * @return 删除结果
     */
    @Override
    public boolean deleteMessageById(Long id) {
        String sql = "DELETE FROM message WHERE id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        
        try {
            rows = RUNNER.update(
                connection,
                sql,
                id
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 删除用户的所有消息
     * @param userId 用户ID
     * @return 删除结果
     */
    @Override
    public boolean deleteMessagesByUserId(String userId) {
        String sql = "DELETE FROM message WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        
        try {
            rows = RUNNER.update(
                connection,
                sql,
                userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }
    
    /**
     * 从ResultSet映射到Message对象
     * @param rs ResultSet
     * @return Message对象
     * @throws SQLException SQL异常
     */
    private Message mapResultSetToMessage(ResultSet rs) throws SQLException {
        Message message = new Message();
        message.setId(rs.getLong("id"));
        message.setUserId(rs.getString("user_id"));
        message.setTitle(rs.getString("title"));
        message.setContent(rs.getString("content"));
        message.setType(rs.getString("type"));
        message.setRelatedId(rs.getString("related_id"));
        message.setIsRead(rs.getInt("is_read") == 1);
        message.setCreateTime(rs.getTimestamp("create_time"));
        
        Timestamp updateTime = rs.getTimestamp("update_time");
        if (updateTime != null) {
            message.setUpdateTime(updateTime);
        }
        
        return message;
    }
    
    /**
     * 关闭资源
     * @param rs ResultSet
     * @param stmt PreparedStatement
     */
    private void closeResources(ResultSet rs, PreparedStatement stmt) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
} 